上一篇講完了會用到的 SQL 語法，本篇會將前面講過的基本功能獨立出來寫成一個 class ，以便需要時可以重複使用。

首先，在 Swift 中要使用 SQLite 時須額外加入相關的函式庫。

import SQLite3

建立一個型態為 OpaquePointer 的變數用於與資料庫連線。

class Sqlite {
  var dbPtr: OpaquePointer? = nil
  ...
}

透過 class 的 init function 來建立存於裝置內的資料庫檔案，讓初始化此 class 時即可建立或開啟該檔案。

class Sqlite {
  ...
  init?(path: String) {
    sqlitePath = path
    dbPtr = self.openDatabase(path: sqlitePath)
  }
  
  func openDatabase(path: String) -> OpaquePointer {
    var connectdb: OpaquePointer? = nil
    var dbStatus: Int32 = SQLITE_ERROR
    
    dbStatus = sqlite3_open(path, &connectdb)
    if dbStatus != SQLITE_OK {
      print("Unable to open database. Error code:", dbStatus)
    }
    return connectdb!
  }
  ...
}

建立資料庫表單，執行建立表單之 SQL 語法是透過 sqlite3_exec()，該 function 會回傳執行結果，可用來判斷是否成功。

class Sqlite {
  ...
  func createTable(_ tableName: String, columnsInfo: [String]) -> Int32 {
    var dbStatus: Int32 = SQLITE_ERROR
    
    let sqlCmd: String = "create table if not exists \(tableName) "
                         + "(\(columnsInfo.joined(separator: ",")))"
                         
    dbStatus = sqlite3_exec(self.dbPtr, String(sqlCmd), nil, nil, nil)
    if dbStatus == SQLITE_OK {
      print("Create table success.")
    }
    return dbStatus
  }
  ...
}

新增資料至特定表單：

class Sqlite {
  ...
  func insert(_ tableName: String, rowInfo: [String: String]) -> Int32 {
    var statement: OpaquePointer? = nil
    var dbStatus: Int32 = SQLITE_ERROR
    
    let sqlCmd: String = "insert into \(tableName) "
                         + "(\(rowInfo.keys.joined(separator: ","))) "
                         + "values (\(rowInfo.values.joined(separator: ",")))"
    dbStatus = sqlite3_prepare_v2(self.dbPtr, String(sqlCmd), -1, &statement, nil)
    if dbStatus == SQLITE_OK {
      if sqlite3_step(statement) == SQLITE_DONE {
        print("Insert data success.")
        return dbStatus
      }
      sqlite3_finalize(statement)
    }
    return dbStatus
  }
  ...
}

讀取表單資料：

class Sqlite {
  ...
    func fetch(_ tableName: String, cond: String?, sortBy order: String?, offset: Int?) -> OpaquePointer {
    var statement: OpaquePointer? = nil
    var dbStatus: Int32 = SQLITE_ERROR
    
    var sqlCmd: String = "select * from \(tableName)"
    if let condition = cond {
      sqlCmd += " where \(condition)"
    }
    
    if let orderBy = order {
      sqlCmd += " order by \(orderBy)"
    }
    
    sqlCmd += " limit \(rowCount)"
    
    if let offsetNum = offset {
      sqlCmd += " OFFSET \(offsetNum)"
    }
    
    dbStatus = sqlite3_prepare_v2(self.dbPtr, String(sqlCmd), -1, &statement, nil)
    print("Fatch data status:", dbStatus)
    return statement!
  }
  ...  
}

更新資料：

class Sqlite {
  ...
  func update(_ tableName: String, cond: String?, rowInfo: [String: String]) -> Int32 {
    var statement: OpaquePointer? = nil
    var dbStatus: Int32 = SQLITE_ERROR
    
    var sqlCmd: String = "update \(tableName) set "
    
    // row info
    var info: [String] = []
    for (key, value) in rowInfo {
      info.append("\(key) = \(value)")
    }
    sqlCmd += info.joined(separator: ",")
    
    if let condition = cond {
      sqlCmd += " where \(condition)"
    }

    dbStatus = sqlite3_prepare_v2(self.dbPtr, String(sqlCmd), -1, &statement, nil)
    if dbStatus == SQLITE_OK && sqlite3_step(statement) == SQLITE_DONE {
      print("Updata data success.")
      return dbStatus
    }
    sqlite3_finalize(statement)
    return dbStatus
  }
  ...
}

刪除資料：

class Sqlite {
  ...
func delete(_ tableName: String, cond: String?) -> Int32 {
    var statement: OpaquePointer? = nil
    var dbStatus: Int32 = SQLITE_ERROR
    
    var sqlCmd: String = "delete from \(tableName)"
    
    if let condition = cond {
      sqlCmd += " where \(condition)"
    }

    dbStatus = sqlite3_prepare_v2(self.dbPtr, String(sqlCmd), -1, &statement, nil)
    if dbStatus == SQLITE_OK && sqlite3_step(statement) == SQLITE_DONE {
      print("Delete data success.")
      return dbStatus
    }
    sqlite3_finalize(statement)
    return dbStatus
  }
  ...
}

Additional
執行 SQL 語法時可以使用 sqlite3_exec() 或 sqlite3_prepare_v2()。前者其實是sqlite3_prepare_v2() ， sqlite3_step() 和 sqlite3_finalize() 的封裝。
sqlite3_prepare_v2() 並不執行 SQL 指令，而是將資料庫轉換到 prepare statement。接下來再透過sqlite3_step() 得到執行 SQL 指令後的結果。而離開 prepare statement 的方式則是呼叫 sqlite3_finalize()。

Reference
SQLite